#!/usr/bin/env python
# coding:utf-8
"""
Created on 2017/6/29 15:12

base Info
"""
__author__ = 'kumikoda'
__version__ = '1.0'

import sys

reload(sys)
sys.setdefaultencoding('utf8')

from modules import dbs
from modules import confs
from openpyxl import load_workbook
#from openpyxl.cell import get_column_letter

class XLRD(object):
    def __init__(self, filename):
        self.filename = filename
        self.wb = load_workbook(self.filename, data_only=True)
        self.sheets = self.wb.sheetnames
        self.ws = self.wb.get_sheet_by_name(self.sheets[0])
        self.country = self.ws
        self.conn, self.cur = dbs.db_connect()

    ## 把excel导入数据库
    def load_excel(self):
        #book = xlrd.open_workbook('The_assets_total_20170609_AWS_AE.xlsx')
        #wb = load_workbook(os.path.join(file_dir, new_filename),data_only=True)

        query_cmdb = '''
                INSERT INTO cmdb (
                assets_number,
                IDC_type,
                room_info,
                cabinet_info,
                serial_number,
                model_number,
                wan_ip,
                ip,
                remote_ip,
                STATUS,
                assets_user,
                business_name,
                business_type,
                detailed_action,
                upline_time,
                downline_time,
                osystem,
                source,
                deadline,
                contract_number,
                #price,
                #monthly_fee,
                up_time,
                down_time,
                #usage_days,
                #monthbilling,
                COMMENT,
                cpu,
                mem,
                disk
                )
                VALUES
                (
                    %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s
                )'''

        # 把表格进行拆分，并成立两张表
        query_money = '''
                INSERT INTO money(
                                          assets_number,
                                          price,
                                          monthly_fee,
                                          usage_days,
                                          monthbilling,
                                          billing_time
                            )
                            VALUES
                                    (
                                        %s,%s,%s,%s,%s,%s
                            )'''


        # 获取最大的行数和列数
        rows = self.ws.max_row
        #columns = self.ws.max_column
        #print rows, columns



        # 建立存储数据的字典
        data_dict_cmdb = []
        data_dict_money = []

        # 把数据存到字典
        for r in range(2, rows + 1):
            #for c in range(2, columns + 1):
                assets_number = self.ws.cell(row=r,column=1).value
                IDC_type = self.ws.cell(row=r,column=2).value
                room_info = self.ws.cell(row=r,column=3).value
                cabinet_info = self.ws.cell(row=r,column=4).value
                serial_number = self.ws.cell(row=r,column=5).value
                model_number = self.ws.cell(row=r,column=6).value
                wan_ip = self.ws.cell(row=r,column=7).value
                ip = self.ws.cell(row=r,column=8).value
                remote_ip = self.ws.cell(row=r,column=9).value
                status = self.ws.cell(row=r,column=10).value
                assets_user = self.ws.cell(row=r,column=11).value
                business_type = self.ws.cell(row=r,column=12).value
                business_name = self.ws.cell(row=r,column=13).value
                detailed_action = self.ws.cell(row=r,column=14).value
                upline_time = self.ws.cell(row=r,column=15).value
                downline_time = self.ws.cell(row=r,column=16).value
                osystem = self.ws.cell(row=r,column=17).value
                source = self.ws.cell(row=r,column=18).value
                deadline = self.ws.cell(row=r,column=19).value
                contract_number = self.ws.cell(row=r,column=20).value
                price = self.ws.cell(row=r,column=21).value
                monthly_fee = self.ws.cell(row=r,column=22).value
                up_time = self.ws.cell(row=r,column=23).value
                down_time = self.ws.cell(row=r,column=24).value
                usage_days = self.ws.cell(row=r,column=25).value
                monthbilling = self.ws.cell(row=r,column=26).value
                comment = self.ws.cell(row=r,column=27).value
                cpu = self.ws.cell(row=r,column=28).value
                mem = self.ws.cell(row=r,column=29).value
                disk = self.ws.cell(row=r,column=30).value
                billing_time = self.ws.cell(row=r,column=31).value

                temp_list_cmdb = [
                    assets_number, IDC_type, room_info, cabinet_info, serial_number, model_number, wan_ip, ip, remote_ip,
                    status,
                    assets_user, business_name, business_type, detailed_action, upline_time, downline_time, osystem, source,
                    deadline,
                    contract_number, up_time, down_time, comment, cpu, mem,
                    disk
                ]

                temp_list_money = [assets_number, price, monthly_fee, usage_days, monthbilling, billing_time]

                values_cmdb = (
                    assets_number, IDC_type, room_info, cabinet_info, serial_number, model_number, wan_ip, ip, remote_ip,
                    status,
                    assets_user, business_name, business_type, detailed_action, upline_time, downline_time, osystem, source,
                    deadline,
                    contract_number, up_time, down_time, comment, cpu, mem,
                    disk
                )

                values_money = (assets_number, price,monthly_fee,usage_days, monthbilling, billing_time)

                data_dict_cmdb.append(temp_list_cmdb)
                data_dict_money.append(temp_list_money)

                self.cur.execute(query_cmdb, values_cmdb)
                self.cur.execute(query_money, values_money)
                data_dict_cmdb = []
                data_dict_money = []
        #print data_dict
        self.conn.commit()
        self.cur.close()
        self.conn.close()
        return {'code':58, 'message': confs._code.get(58, '用户导入数据成功')}

def main():
    x = XLRD(r'The_assets_total_20170609_AWS_AE.xlsx')
    x.load_excel()

if __name__ == '__main__':
    main()